"""Initial migration

Revision ID: initial_migration
Revises: 
Create Date: 2024-01-20 10:00:00.000000

"""
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = 'initial_migration'
down_revision = None
branch_labels = None
depends_on = None

def upgrade():
    # 创建性别枚举类型
    op.execute("CREATE TYPE gender AS ENUM ('male', 'female')")
    
    # 创建学生表
    op.create_table(
        'students',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('student_id', sa.String(), nullable=False),
        sa.Column('name', sa.String(), nullable=False),
        sa.Column('email', sa.String(), nullable=False),
        sa.Column('hashed_password', sa.String(), nullable=False),
        sa.Column('gender', sa.Enum('male', 'female', name='gender'), nullable=False),
        sa.Column('grade', sa.Integer(), nullable=False),
        sa.Column('major', sa.String(), nullable=False),
        sa.Column('sleeping_habit', sa.String(), nullable=True),
        sa.Column('cleanliness', sa.String(), nullable=True),
        sa.Column('is_active', sa.Boolean(), nullable=False, server_default='true'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('student_id'),
        sa.UniqueConstraint('email')
    )
    
    # 创建兴趣表
    op.create_table(
        'interests',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(), nullable=False),
        sa.Column('description', sa.String(), nullable=True),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('name')
    )
    
    # 创建学生兴趣关联表
    op.create_table(
        'student_interests',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('student_id', sa.Integer(), nullable=False),
        sa.Column('interest_id', sa.Integer(), nullable=False),
        sa.Column('interest_level', sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(['interest_id'], ['interests.id'], ),
        sa.ForeignKeyConstraint(['student_id'], ['students.id'], ),
        sa.PrimaryKeyConstraint('id')
    )
    
    # 创建互选偏好表
    op.create_table(
        'preferences',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('student_id', sa.Integer(), nullable=False),
        sa.Column('preferred_student_id', sa.Integer(), nullable=False),
        sa.Column('preference_level', sa.Integer(), nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()')),
        sa.ForeignKeyConstraint(['preferred_student_id'], ['students.id'], ),
        sa.ForeignKeyConstraint(['student_id'], ['students.id'], ),
        sa.PrimaryKeyConstraint('id')
    )
    
    # 创建宿舍楼表
    op.create_table(
        'buildings',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(), nullable=False),
        sa.Column('floors', sa.Integer(), nullable=False),
        sa.Column('rooms_per_floor', sa.Integer(), nullable=False),
        sa.Column('gender', sa.Enum('male', 'female', name='gender'), nullable=False),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('name')
    )
    
    # 创建房间表
    op.create_table(
        'rooms',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('building_id', sa.Integer(), nullable=False),
        sa.Column('floor', sa.Integer(), nullable=False),
        sa.Column('room_number', sa.String(), nullable=False),
        sa.Column('capacity', sa.Integer(), nullable=False),
        sa.Column('current_occupancy', sa.Integer(), nullable=False, server_default='0'),
        sa.ForeignKeyConstraint(['building_id'], ['buildings.id'], ),
        sa.PrimaryKeyConstraint('id')
    )
    
    # 创建房间分配表
    op.create_table(
        'room_assignments',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('student_id', sa.Integer(), nullable=False),
        sa.Column('room_id', sa.Integer(), nullable=False),
        sa.Column('assigned_at', sa.DateTime(timezone=True), server_default=sa.text('now()')),
        sa.Column('is_active', sa.Boolean(), nullable=False, server_default='true'),
        sa.ForeignKeyConstraint(['room_id'], ['rooms.id'], ),
        sa.ForeignKeyConstraint(['student_id'], ['students.id'], ),
        sa.PrimaryKeyConstraint('id')
    )
    
    # 创建调宿申请表
    op.create_table(
        'swap_requests',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('requester_id', sa.Integer(), nullable=False),
        sa.Column('target_id', sa.Integer(), nullable=False),
        sa.Column('status', sa.String(), nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()')),
        sa.Column('updated_at', sa.DateTime(timezone=True), onupdate=sa.text('now()')),
        sa.ForeignKeyConstraint(['requester_id'], ['students.id'], ),
        sa.ForeignKeyConstraint(['target_id'], ['students.id'], ),
        sa.PrimaryKeyConstraint('id')
    )
    
    # 创建索引
    op.create_index(op.f('ix_students_id'), 'students', ['id'], unique=False)
    op.create_index(op.f('ix_students_student_id'), 'students', ['student_id'], unique=True)
    op.create_index(op.f('ix_students_email'), 'students', ['email'], unique=True)
    op.create_index(op.f('ix_interests_id'), 'interests', ['id'], unique=False)
    op.create_index(op.f('ix_student_interests_id'), 'student_interests', ['id'], unique=False)
    op.create_index(op.f('ix_preferences_id'), 'preferences', ['id'], unique=False)
    op.create_index(op.f('ix_buildings_id'), 'buildings', ['id'], unique=False)
    op.create_index(op.f('ix_rooms_id'), 'rooms', ['id'], unique=False)
    op.create_index(op.f('ix_room_assignments_id'), 'room_assignments', ['id'], unique=False)
    op.create_index(op.f('ix_swap_requests_id'), 'swap_requests', ['id'], unique=False)

def downgrade():
    # 删除索引
    op.drop_index(op.f('ix_swap_requests_id'), table_name='swap_requests')
    op.drop_index(op.f('ix_room_assignments_id'), table_name='room_assignments')
    op.drop_index(op.f('ix_rooms_id'), table_name='rooms')
    op.drop_index(op.f('ix_buildings_id'), table_name='buildings')
    op.drop_index(op.f('ix_preferences_id'), table_name='preferences')
    op.drop_index(op.f('ix_student_interests_id'), table_name='student_interests')
    op.drop_index(op.f('ix_interests_id'), table_name='interests')
    op.drop_index(op.f('ix_students_email'), table_name='students')
    op.drop_index(op.f('ix_students_student_id'), table_name='students')
    op.drop_index(op.f('ix_students_id'), table_name='students')
    
    # 删除表
    op.drop_table('swap_requests')
    op.drop_table('room_assignments')
    op.drop_table('rooms')
    op.drop_table('buildings')
    op.drop_table('preferences')
    op.drop_table('student_interests')
    op.drop_table('interests')
    op.drop_table('students')
    
    # 删除枚举类型
    op.execute('DROP TYPE gender') 